//
//  ItemDBO.m
//  POS
//
//  Created by sigma xu on 4/29/13.
//  Copyright (c) 2013 sigma xu. All rights reserved.
//

#import "ItemDBO.h"

@implementation ItemDBO

@synthesize so_id,item_barcode,item_name,item_brand,item_package,quantity,unit,unit_price,create_date;

-(id)init{
    if(self = [super init]){
        
    }
    return self;
}
-(void)open_db{
    NSFileManager *fileMgr = [NSFileManager defaultManager];
    NSString *dbPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"pos.sqlite"];
    BOOL file_exist = [fileMgr fileExistsAtPath:dbPath];
    if (!file_exist) {
        NSLog(@"Cant locate database file '%@'.",dbPath);
    }
    if(!(sqlite3_open([dbPath UTF8String], &posDB)) == SQLITE_OK){
        NSLog(@"Error: '%s'",sqlite3_errmsg(posDB));
    }
}
-(NSMutableArray *) load_item_list_from_so:(NSString *) id_so{
    [self open_db];
    item_list = [[NSMutableArray alloc] init];
    NSString *query = [NSString stringWithFormat:@"SELECT * from so_item_relation where status>0 and so_id = %@",id_so];
    //    NSLog(@"in idbo load_item_list_from_so query is: %@",query);
    if(sqlite3_prepare(posDB, [query UTF8String], -1, &sqlstmt1, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement1 : '%s'",sqlite3_errmsg(posDB));
    }else{
        while (sqlite3_step(sqlstmt1) == SQLITE_ROW) {
            ItemDBO *idbo = [[ItemDBO alloc] init];
            idbo=[self get_item_by_barcode:[NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlstmt1, 2)] unit_code:[[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt1, 4)]];
            
            idbo.so_id = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlstmt1, 1)];
            idbo.quantity = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt1, 3)];
            idbo.unit = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt1, 4)];
            idbo.unit_price = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt1, 5)];
            NSDate *datefromdb=[NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(sqlstmt1, 6)];
            NSDateFormatter *df_utc = [[NSDateFormatter alloc] init];
            [df_utc setTimeZone:[NSTimeZone localTimeZone]];
            [df_utc setDateFormat:@"yyy-MM-dd HH:mm:ss"];
            idbo.create_date = [df_utc stringFromDate:datefromdb];
            [item_list addObject:idbo];
        }
        sqlite3_finalize(sqlstmt1);
    }
    //    NSLog(@"in idbo load_item_list_from_so finished %@",item_list);
    sqlite3_close(posDB);
    return item_list;
}
-(ItemDBO *) get_item_by_barcode:(NSString *) barcode
                       unit_code:(NSString *) unit_code{
    [self open_db];
    ItemDBO *idbo = [[ItemDBO alloc] init];
    NSString *query = [NSString stringWithFormat:@"SELECT * from product where unit = '%@' and barcode = '%@'",unit_code,barcode];
    //    NSLog(@"in idbo get_item_by_barcode query is: %@",query);
    if(sqlite3_prepare(posDB, [query UTF8String], -1, &sqlstmt2, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
        if (sqlite3_step(sqlstmt2) == SQLITE_ROW) {
            idbo.item_barcode = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlstmt2, 0)];
            idbo.item_brand = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt2, 1)];
            idbo.item_name = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt2, 2)];
            idbo.item_package = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt2, 3)];
            idbo.unit = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt2, 4)];
            idbo.unit_price = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt2, 5)];
        }else{
            idbo = NULL;
        }
        sqlite3_finalize(sqlstmt2);
    }
    //    NSLog(@"in idbo with get_item_by_barcode finished");
    sqlite3_close(posDB);
    return idbo;
}
-(void)insert_sales_order_each_item{
    [self open_db];
    const char *sql = "insert into so_item_relation (so_id, item_barcode, quantity, unit, unit_price, create_date) values (?,?,?,?,?,?)";
    if(sqlite3_prepare(posDB, sql, -1, &sqlstmt1, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
        sqlite3_bind_text(sqlstmt1, 1, [so_id UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt1, 2, [item_barcode  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt1, 3, [quantity  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt1, 4, [unit  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt1, 5, [unit_price  UTF8String], -1, SQLITE_TRANSIENT);
        NSDate *now=[[NSDate alloc]init];
        sqlite3_bind_double(sqlstmt1, 6, [now timeIntervalSince1970]);
        if(SQLITE_DONE != sqlite3_step(sqlstmt1))
            NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
        else{
            sqlite3_last_insert_rowid(posDB);
        }
        sqlite3_reset(sqlstmt1);
    }
    sqlite3_close(posDB);
}
-(void)insert_item{
    [self open_db];
    const char *sql = "insert into product (barcode, brand, name, package_size, unit, unit_price) values (?,?,?,?,?,?)";
    if(sqlite3_prepare(posDB, sql, -1, &sqlstmt1, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
        sqlite3_bind_text(sqlstmt1, 1, [item_barcode UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt1, 2, [item_brand  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt1, 3, [item_name  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt1, 4, [item_package  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt1, 5, [unit  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt1, 6, [unit_price  UTF8String], -1, SQLITE_TRANSIENT);
        if(SQLITE_DONE != sqlite3_step(sqlstmt1))
            NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
        else{
            sqlite3_last_insert_rowid(posDB);
        }
        sqlite3_reset(sqlstmt1);
    }
    sqlite3_close(posDB);
}
-(void) check_update_so_item{
    [self open_db];
    int relation_id = [self check_sales_order_each_item];
    if (relation_id==0) {
        [self insert_sales_order_each_item];
    }else{
        [self update_exit_sales_order_each_item:relation_id];
    }
    sqlite3_close(posDB);
}
-(int) check_sales_order_each_item{
    [self open_db];
    int relation_id =0;
    NSString *query = [NSString stringWithFormat:@"SELECT * from so_item_relation where so_id = '%@' and item_barcode = '%@'", so_id, item_barcode];
    const char *sql = [query UTF8String];
    if(sqlite3_prepare(posDB, sql, -1, &checkstmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
        if (sqlite3_step(checkstmt) == SQLITE_ROW) {
            relation_id = sqlite3_column_int(checkstmt, 0);
        }
        sqlite3_reset(checkstmt);
    }
    sqlite3_close(posDB);
    return relation_id;
}
-(void) update_exit_sales_order_each_item:(int) relation_id{
    [self open_db];
    NSString *query = [NSString stringWithFormat:@"update so_item_relation Set quantity='%@', unit='%@', unit_price='%@', status=1 Where id_so_item_relation='%d'",quantity,unit,unit_price,relation_id];
    const char *sql = [query UTF8String];
    if(sqlite3_prepare(posDB, sql, -1, &updatestmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
        while (sqlite3_step(updatestmt) == SQLITE_ROW) {
        }
        sqlite3_reset(updatestmt);
    }
    sqlite3_close(posDB);
}
-(void) delete_sales_order_each_item{
    [self open_db];
    NSString *query = [NSString stringWithFormat:@"update so_item_relation Set status = 0 Where so_id = '%@'",so_id];
    const char *sql = [query UTF8String];
    if(sqlite3_prepare(posDB, sql, -1, &updatestmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
        while (sqlite3_step(updatestmt) == SQLITE_ROW) {
        }
        sqlite3_reset(updatestmt);
    }
    sqlite3_close(posDB);
}
-(void)clear_item{
    [self open_db];
    const char *dropsql = "drop table product";
    if(sqlite3_exec(posDB, dropsql, NULL, NULL, NULL) == SQLITE_OK){
        NSLog(@"Table product Delete");
    }
    const char *createsql = "CREATE TABLE product (barcode VARCHAR,brand VARCHAR, name VARCHAR, package_size VARCHAR, unit VARCHAR, unit_price VARCHAR)";
    if(sqlite3_exec(posDB, createsql, NULL, NULL, NULL) == SQLITE_OK){
        NSLog(@"Table product Create");
    }
    sqlite3_close(posDB);
}
-(void)finalize{
    
    [super finalize];
}
@end